<?php
/*
 * TestLink Open Source Project - http://testlink.sourceforge.net/
 * $Id: Migrator.php,v 1.5 2008/01/25 14:49:53 franciscom Exp $
 *
 * BUGID 1328 - LEFT without AS

change notes

author   date     change desc
-------------------------------
affiza   22 Jul 2011  ALMTL-4 ( Bug Fix ) Unable to create requirement link - Integration JIRA & Testlink Failed
		      proceed to change sql syntax to lft outer join, and import all jira issues

 */

require_once("migrate_jira_to_tl_functions.php");
require_once(dirname(__FILE__) . "/../../config.inc.php");

class JMigrator
{

	private $source_db;
	private $target_db;


	function __construct($source_db, $target_db)
	{
		$this->source_db = $source_db;
		$this->target_db = $target_db;
	}
	

	function jira_requirements_migration()
	{
		$hhmmss=date("H:i:s");
		/*echo "<a onclick=\"return DetailController.toggle('details-jirareqtable')\" href=\"tplan/\">
		<img src='../img/icon-foldout.gif' align='top' title='show/hide'> Requirements: {$this->msg_click_to_show} {$hhmmss}</a>";
		echo '<div class="detail-container" id="details-jirareqtable" style="display: none;">';*/

		/* chin $sql="SELECT JI.pkey,JI.SUMMARY,JI.DESCRIPTION, PJ.pkey as pjkey FROM jiraissue JI join project PJ on PJ.ID = JI.PROJECT " . 
		     " WHERE JI.issuetype = 2 OR JI.issuetype = 4 OR JI.issuetype = 5 OR JI.issuetype = 6 GROUP BY JI.pkey"; */
			 
		/*$sql="SELECT JI.pkey,JI.SUMMARY,IT.pname as ISSUETYPE, PJ.pkey as pjkey, PJ.pname as PNAME , CFV.TEXTVALUE as DESCRIPTION FROM jiraissue JI " .
		     " JOIN project PJ ON PJ.ID = JI.PROJECT " . 
             " JOIN customfieldvalue CFV ON  CFV.ISSUE= JI.ID " .		
			 " JOIN issuetype IT ON  IT.ID= JI.issuetype " .
		     " WHERE JI.issuetype = 2 OR JI.issuetype = 4 OR JI.issuetype = 5 OR JI.issuetype = 6 GROUP BY JI.pkey";			 */

		$sql="select ji.pkey as pkey,ji.summary as SUMMARY, it.pname as ISSUETYPE, pj.pkey as pjkey, pj.pname as PNAME , cfv.TEXTVALUE as DESCRIPTION" .
		" from jiraissue ji left join customfieldvalue cfv on ji.ID=cfv.ISSUE, project pj, issuetype it " .
		" where ji.project = pj.id " .
		" and ji.issuetype = it.id " .
		//" and ji.issuetype in ('2','4','5','6') " . # import all issues from JIRA
		" group by ji.pkey"; 


		//$sql="select * from jiraissue";
		$req=$this->source_db->fetchRowsIntoMap($sql,'pkey');
		if(is_null($req)) 
		{
			echo "There are no requirements to be migrated!";
		}
		else
		{
		  $this->migrate_jira_requirements($this->source_db,$this->target_db,$req);
		}
		//echo "</div><p>";
	}
	
	function migrate_jira_requirements(&$source_db,&$target_db,&$req)
	{
  
		$req_qty=count($req);
		print_msg("<pre>Number of requirements: " . $req_qty . "</pre>");
        foreach($req as $req_id => $rdata)
		{
			$pjkey = $rdata['pjkey'];
			$pkey = $rdata['pkey'];
			$sum = $rdata['SUMMARY'];
			$pname = $rdata['PNAME'];
			$desp = $pname . "-" . $pkey . "-[".$rdata['ISSUETYPE']."]-".$rdata['DESCRIPTION'];
		    //echo "<pre>desp mjr: " . $desp ;echo "</pre>";
			//echo "<pre>sum mjr: " . $sum ;echo "</pre>";
			//echo "<pre>sum mjr: " . $sum ;echo "</pre>";
			print_msg("<pre>pkey: " . $pkey . " pjkey: " . $pjkey . "</pre>");
			$sql="SELECT id FROM " . DB_TABLE_PREFIX . "nodes_hierarchy" .
			     " WHERE name = '" . $rdata['pjkey'] . "'";
			$treq=$this->target_db->fetchRowsIntoMap($sql,'id');
			if(is_null($treq)) 
			{
				// Do nothing if id not found
				//echo "<span class='notok'>There are no id found!</span></b>";
			}
			else
			{
				foreach($treq as $treq_id => $trdata)
				{
					$rspid = $trdata['id'];
					//echo "<pre>rspid: " . $rspid ;echo "</pre>";
					$result = mysql_query("SELECT * FROM " . DB_TABLE_PREFIX . "requirements WHERE req_doc_id='{$pkey}'");
					//echo "<pre>sql: " . $sql ;echo "</pre>";
					if($row = mysql_fetch_array($result)) 
					{
						print_msg("Do update");
						//echo "<pre>Do update </pre>";
						$this->update_testlink_db($pjkey,$pkey,$sum,$desp,$rspid);
					}
					else
					{ 
						print_msg("Do INSERT");
						//echo "<pre>Do insert </pre>";
						$this->insert_testlink_db($pjkey,$pkey,$sum,$desp,$rspid);
					}
				}
			}
		}
	} // end function
	
	function update_testlink_db(&$pjkey,&$pkey,&$sum,&$desp,&$rspid)
	{
		$sql1="SELECT id FROM " . DB_TABLE_PREFIX . "requirements WHERE req_doc_id='{$pkey}'";
		//echo "<pre>sql1: " . $sql1 ;echo "</pre>";
		$result = mysql_query($sql1);
		if (!$result) {
			echo 'Could not run query: ' . mysql_error();
			exit;
		}
		$row = mysql_fetch_row($result);
		$nhid = $row[0]; 	
		// echo $nhid; 	
        $sql2="UPDATE ". DB_TABLE_PREFIX . "nodes_hierarchy" .
		      " SET name='{$sum}' " .
			  " WHERE id={$nhid}";
        $this->target_db->exec_query($sql2); 			  
		$sql3="SELECT id FROM " . DB_TABLE_PREFIX . "nodes_hierarchy WHERE parent_id='{$nhid}'";
		$result = mysql_query($sql3);
		if (!$result) {
			echo 'Could not run query: ' . mysql_error();
			exit;
		}
		$row = mysql_fetch_row($result);
		$rvid = $row[0]; 	
		// echo $rvid;
		//echo "<pre>desp: " . $desp ;echo "</pre>";
        $sql4="UPDATE ". DB_TABLE_PREFIX . "req_versions" .
		      " SET scope='{$desp}' , modification_ts=CURRENT_TIMESTAMP()" .
			  " WHERE id={$rvid}";
        $this->target_db->exec_query($sql4); 			  

		
	}
	
	function insert_testlink_db(&$pjkey,&$pkey,&$sum,&$desp,&$rspid)
	{
	    //echo "<pre> insert id: " . $rspid ; echo "</pre>";
		$sql1="INSERT INTO " . DB_TABLE_PREFIX . "nodes_hierarchy" .
			 " (id,name,parent_id,node_type_id,node_order";
		$values1=" VALUES(NULL,'$sum','$rspid','7','1'";
		$sql1 .=") " . $values1 . ")";
		//echo "<pre> sql1: " . $sql1;echo "</pre>";
		$this->target_db->exec_query($sql1);
		$exec_id1 = mysql_insert_id();

		//echo "<pre> exec_id1: " . $exec_id1 ;echo "</pre>";
		//$exec_id1 = 0;$exec_id2 = 0;
		$sql2="INSERT INTO " . DB_TABLE_PREFIX . "nodes_hierarchy" .
			 " (id,name,parent_id,node_type_id,node_order";
		$values2=" VALUES(NULL,NULL,'$exec_id1','8','1'";
		$sql2 .=") " . $values2 . ")";
		//echo "<pre> sql2: " . $sql2;echo "</pre>";
		$this->target_db->exec_query($sql2);
		$exec_id2 = mysql_insert_id();
		//echo "<pre> exec_id2: " . $exec_id2;echo "</pre>";

		$sql3="INSERT INTO " . DB_TABLE_PREFIX . "requirements" .
			 " (id,srs_id,req_doc_id";
		$values3=" VALUES('$exec_id1','$rspid','$pkey'";
		$sql3 .=") " . $values3 . ")";
		$this->target_db->exec_query($sql3);
		
		$sql4="INSERT INTO " . DB_TABLE_PREFIX . "req_versions" .
			 " (id,version,scope,status,type,active,is_open,expected_coverage,author_id,creation_ts,modifier_id,modification_ts";
		$values4=" VALUES('$exec_id2','1','$desp','V','3','1','1','1','1',CURRENT_TIMESTAMP(),'1',CURRENT_TIMESTAMP()";
		$sql4 .=") " . $values4 . ")";
		$this->target_db->exec_query($sql4);
	}

}
?>
